/*
Copyright 2008  Four Roads LLC conact@4-roads.com

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either 
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public 
License along with this library.  If not, see <http://www.gnu.org/licenses/>.
*/
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommunityServer;
using CommunityServer.Components;
using CommunityServer.Data;

namespace FourRoads.Links.DataProvider
{
	/// <summary>
	/// Summary description for ResLinkSqlDataProvider.
	/// </summary>
	public class ResLinkSqlDataProvider : ResLinkDataProvider
	{
		public ResLinkSqlDataProvider()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		#region Member variables
		
		protected string databaseOwner	= "dbo";	// overwrite in web.config
		string connectionString = null;
		ProviderHelper sqlHelper = null;
		#endregion

		#region Constructor
		/****************************************************************
		// SqlDataProvider
		//
		/// <summary>
		/// Class constructor
		/// </summary>
		//
		****************************************************************/
		public ResLinkSqlDataProvider(string databaseOwner, string connectionString) 
		{

			// Read the connection string for this provider
			//
			this.connectionString = connectionString;

			// Read the database owner name for this provider
			//
			this.databaseOwner = databaseOwner;

			sqlHelper = ProviderHelper.Instance();

		}
		#endregion
	
		#region SettingsID 
		protected int GetSettingsID()
		{
			return  sqlHelper.GetSettingsID();
		}

		protected SqlParameter SettingsIDParameter()
		{
			SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
			p.Value = GetSettingsID();
			return p;
		}
		#endregion

		#region Link Comments

		public override int AddComment(ResLinkComments comment)
		{
			return InsertUpdateLinkComment(comment , true);
		}

		public override void UpdateComment(ResLinkComments comment)
		{
			InsertUpdateLinkComment(comment , false);
		}
	
		public override void DeleteComment(int sectionID ,int postID , int parentID, string reason)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkComment_Delete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = parentID;
				myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar).Value = reason;
				myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = CSContext.Current.User.UserID;
				myCommand.Parameters.Add("@SectionId", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		private int InsertUpdateLinkComment(ResLinkComments comment , bool insert)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkComment_InsertUpdate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = comment.SectionID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = comment.ParentID;
				myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = comment.User.UserID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = comment.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = comment.FormattedBody;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = comment.IsApproved ? 1 : 0;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar).Value = comment.UserHostAddress;
				myCommand.Parameters.Add("@Rating", SqlDbType.Int).Value = comment.CommentRating;

				if(insert)
				{
					SqlParameter p = new SqlParameter("@PostId", SqlDbType.Int);

					p.Direction = ParameterDirection.InputOutput;
					p.Value = 0;
				
					myCommand.Parameters.Add(p);
				}
				else
				{
					myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = comment.PostID;
				}

				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				if (insert)
					comment.PostID  = (int) myCommand.Parameters["@PostId"].Value;

				return comment.PostID;
			}
		}

		public override ArrayList GetLinkComments(int sectionID,int postId, bool viewUnmoderated)
		{
			ArrayList comments = new ArrayList();

			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkSection_GetLinkComments", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
			
				myCommand.Parameters.Add("@ParentPostID", SqlDbType.Int).Value = postId;
				myCommand.Parameters.Add("@ViewUnmoderated", SqlDbType.Int).Value = viewUnmoderated? 1 : 0;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{

					ResLinkComments rlc = null;
					// Get the requested Permissions
					while (dr.Read()) 
					{
						rlc = CreateLinkCommentInformationFromIDataReader(dr);
						comments.Add(rlc);
					}
				}
				return comments;
			}
		}
		#endregion

		#region Sections

		public override void GetSectionDetails(ref ResLinkSection sectionData)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkSection_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
		
				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionData.SectionID;
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.Int).Value = ExApplicationType.ResLink;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{

					// Get the requested sections
					if(dr.Read()) 
					{
						// Add the album to the Hashtable
						CreateRootSectionInformationFromIDataReader(dr , ref sectionData);
					}

					dr.NextResult();

					// Get the requested Permissions
					while (dr.Read()) 
					{
						AddSectionPermissions(dr , ref sectionData);
					}

					dr.NextResult();

					// Get the Child sections ids the data will be populated if required
					while (dr.Read()) 
					{
						ResLinkSection childSec = new ResLinkSection( System.Convert.ToInt32(dr["SectionID"]) );
						childSec.Name = (string)dr["Name"];
						childSec.IsActive = Convert.ToBoolean(dr["IsActive"]);
						childSec.ApplicationKey = (string)dr["ApplicationKey"];
						sectionData.Sections.Add(childSec);
					}

					//Get the number of links under this section
					dr.NextResult();
					dr.Read();

					sectionData.FullSubLinkCount = (int)dr["LinkCount"];
				}
			}
		}

		public override ResLinkSection GetSectionDetails(int sectionID)
		{
			ResLinkSection returnedSection = new ResLinkSection();
			returnedSection.SectionID = sectionID;

			GetSectionDetails(ref returnedSection);

			return returnedSection;
		}

		public override int InsertNewSection(ResLinkSection section)
		{
			CommonDataProvider cdp = CommonDataProvider.Instance();

			section.ApplicationType = (ApplicationType)ExApplicationType.ResLink;

			return cdp.CreateUpdateDeleteSection(section , DataProviderAction.Create  );
		}

		public override void DeleteSection(ResLinkSection section)
		{
			CommonDataProvider cdp = CommonDataProvider.Instance();

			section.ApplicationType = (ApplicationType)ExApplicationType.ResLink;

			//Make sure we have the whole thing
			ResLinkSection thisSection = ResLinkSection.GetSection(section.SectionID);

			//THis will iterate down the tree backwards delete ing all of the siibliings
			foreach(ResLinkSection children in thisSection.Sections)
			{
				DeleteSection(children);	
			}

			cdp.CreateUpdateDeleteSection(section , DataProviderAction.Delete  );
		}

		public override void UpdateSection(ResLinkSection section)
		{
			CommonDataProvider cdp = CommonDataProvider.Instance();

			section.ApplicationType = (ApplicationType)ExApplicationType.ResLink;

			cdp.CreateUpdateDeleteSection(section , DataProviderAction.Update  );
		}
		#endregion

		#region Links
		private ArrayList GenericLinkQuery(int top , int sectionID , string spName)
		{
			ArrayList links = new ArrayList();

			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + "." + spName, myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
			
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@Top", SqlDbType.Int).Value = top;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;

				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{
					// Get the requested Permissions
					while (dr.Read()) 
					{
						int [] ids = new int[2];
						ids[0] = (int)dr["SectionID"];
						ids[1] = (int)dr["PostID"];
						links.Add(ids);
					}
				}

				return links;
			}
		}

		public override ArrayList GetPopularLinks(int top , int sectionID)
		{
			return GenericLinkQuery(top , sectionID , "cs_ResLink_GetPopularLinks");
		}

		public override ArrayList GetRecentlyUpdatedLinks(int top , int sectionID)
		{
			return GenericLinkQuery(top , sectionID , "cs_ResLink_GetRecentLinks");
		}

		public override void UpdateLinkHitCount(int sectionID , int postId)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkUpdateLinkHitCount", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = CSContext.Current.User.UserID;
				myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = postId;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override void MoveLink(ResLinkLink link , int newSectionID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkMoveLink", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = link.SectionID;
				myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = CSContext.Current.User.UserID;
				myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = link.PostID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@NewSectionId", SqlDbType.Int).Value = newSectionID;

				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

			}
		}


		private int InsertUpdateLink(ResLinkLink link , bool insert)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkLink_InsertUpdate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = link.SectionID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = link.Subject;
				myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = CSContext.Current.User.UserID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = link.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = link.FormattedBody;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = link.IsApproved == true ? 1 : 0;
				myCommand.Parameters.Add("@ReportedDead", SqlDbType.Bit).Value = link.ReportedDead == true ? 1 : 0;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar).Value = link.UserHostAddress;
				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NVarChar).Value = link.GetSerializerData().Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NVarChar).Value =  link.GetSerializerData().Values;
				myCommand.Parameters.Add("@LinkURL", SqlDbType.NVarChar).Value = link.LinkUrl;

				if(insert)
				{

					SqlParameter p = new SqlParameter("@PostId", SqlDbType.Int);

					p.Direction = ParameterDirection.InputOutput;
					p.Value = 0;
				
					myCommand.Parameters.Add(p);
				}
				else
				{
					myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = link.PostID;
				}

				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				if (insert)
					link.PostID  = (int) myCommand.Parameters["@PostId"].Value;

				return link.PostID;
			}
		}

		public override int InsertNewLink( ResLinkLink link)
		{
			return InsertUpdateLink(link , true);
		}

		public override void UpdateLink(ResLinkLink link)
		{
			InsertUpdateLink(link , false);
		}

		public override ArrayList GetPagedLinks(int sectionId, bool viewUnmoderated , bool viewDead , int page , int pageCount, LinkViewSort sort , out int moreRecords)
		{
			ArrayList links = new ArrayList();

			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkSection_GetLinks", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
			
				myCommand.Parameters.Add("@SectionId", SqlDbType.Int).Value = sectionId;
				myCommand.Parameters.Add("@ViewUnmoderated", SqlDbType.Bit).Value = viewUnmoderated ? 1 : 0;
				myCommand.Parameters.Add("@ViewDead", SqlDbType.Bit).Value = viewDead ? 1 : 0;
				myCommand.Parameters.Add("@PromoteLinks", SqlDbType.Bit).Value = ResLinkConfiguration.Instance().PromoteLinksIntoParentSections ? 1 : 0;
				myCommand.Parameters.Add("@Page", SqlDbType.Int).Value = page;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = (int)sort;
				myCommand.Parameters.Add("@PageCount", SqlDbType.Int).Value = pageCount;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{
					ResLinkLink rl = null;
					// Get the requested Permissions
					while (dr.Read()) 
					{
						rl = CreateLinksInformationFromIDataReader(dr);
						links.Add(rl);
					}

					dr.NextResult();
					dr.Read();

					moreRecords = (int)dr["MoreRecords"];
				}
				return links;
			}
		}
		
		public override ArrayList GetLinks(int sectionId , bool viewUnmoderated , bool viewDead)
		{
			int moreRecords;
			return GetPagedLinks(sectionId, viewUnmoderated , viewDead , -1 , -1  , LinkViewSort.Name , out moreRecords);
		}

		public override ArrayList GetLinks(int sectionId , bool viewUnmoderated , bool viewDead, LinkViewSort sort)
		{
			int moreRecords;
			return GetPagedLinks(sectionId, viewUnmoderated , viewDead , -1 , -1  , sort, out moreRecords);
		}

		public override ResLinkLink GetLink(int sectionId , int postId)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkSection_GetLink", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
			
				myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = postId;
				myCommand.Parameters.Add("@SectionId", SqlDbType.Int).Value = sectionId;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{
					ResLinkLink rl = null;
					// Get the requested Permissions
					if (dr.Read()) 
					{
						rl = CreateLinksInformationFromIDataReader(dr);
					}
					
					return rl;
				}
			}
		}

		public override void DeleteLink(ResLinkLink link)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkLink_Delete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@PostId", SqlDbType.Int).Value = link.PostID;
				myCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = CSContext.Current.User.UserID;
				myCommand.Parameters.Add("@SectionId", SqlDbType.Int).Value = link.SectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override void ReportDeadLink(int postId , int userId)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkMarkDead", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postId;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userId;

				myConnection.Open();

				myCommand.ExecuteNonQuery();
			}
		}

		#endregion

		#region BreadCrumb

		public override ArrayList GetParentBreadCrumbs(ResLinkSection section)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkBreadCrumbGet", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList items = new ArrayList();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = section.SectionID;
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.Int).Value = ExApplicationType.ResLink;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{
					// Get the requested Permissions
					while (dr.Read()) 
					{
						AddBasicSection(dr , ref items);
					}
				}

				return items;
			}
		}
		#endregion

		#region Moderation

		public override ArrayList GetDeadLinkSummary()
		{
			//returns an array of basic summary objects the key contains the number of links that require moderation
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkDeadLinksSummary", myConnection);
				ArrayList summaryList = new ArrayList();

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ExApplicationType.ResLink;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{
					// Get the requested sections
					while(dr.Read()) 
					{
						AddModerationSummary(dr , ref summaryList);
					}
				}

				return summaryList;
			}
		}

		public override ArrayList GetModerationSummary()
		{
			//returns an array of basic summary objects the key contains the number of links that require moderation
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkModerationSummary", myConnection);
				ArrayList summaryList = new ArrayList();

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ExApplicationType.ResLink;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();
				using (SqlDataReader dr = myCommand.ExecuteReader())
				{
					// Get the requested sections
					while(dr.Read()) 
					{
						AddModerationSummary(dr , ref summaryList);
					}
				}

				return summaryList;
			}
		}
		#endregion

		#region Permissions
		public override ArrayList GetResLinkPermissions(int sectionID) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", myConnection);
				Hashtable resLinkPermissions = new Hashtable();
				SqlDataReader reader;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ExApplicationType.ResLink;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();

				using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
				{
					while(reader.Read()) 
					{
						ResLinkPermission rl = new ResLinkPermission();
						CommonDataProvider.PopulatePermissionFromIDataReader (rl,reader);
						rl.SectionID = sectionID;
						rl.SettingsID = this.GetSettingsID();

						// Merge the permission
						if (resLinkPermissions[rl.RoleID] != null) 
						{

						} 
						else 
						{
							resLinkPermissions.Add(rl.RoleID, rl);
						}

					}

					reader.Close();
				}

				myConnection.Close();

				return new ArrayList(resLinkPermissions.Values);
			}
		}

		#endregion

		#region Email
		public override Hashtable GetEmailsTrackedBySectionID(int sectionID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLinkEmails_TrackingSection", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Hashtable users = new Hashtable();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
    
				// Execute the command
				myConnection.Open();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (dr.Read()) 
					{
						User user = new User();
						user.Email = (string) dr["Email"];
						user.EnableHtmlEmail = Convert.ToBoolean(dr["EnableHtmlEmail"]);
				
						if(!Globals.IsNullorEmpty(user.Email))
							users.Add(user.Email,user );
					}

					dr.Close();
				}
				myConnection.Close();

				return users;
			}
		}
		#endregion

		#region search
		/// <summary>
		/// This only returns a list of all section id's regardless of access rights
		/// </summary>
		/// <returns></returns>
		public override ArrayList GetAllActiveSections()
		{
			ArrayList sections = new ArrayList();
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLink_Sections_GetAll", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				
				// Add Parameters to SPROC
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();

				try
				{
					using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
					{
						// Get the requested weblogs
						ResLinkSection section = null;
						while (dr.Read()) 
						{
							//THis creates a section that is labeled as partial
							//not very nice way of doing it
							section = new ResLinkSection((int)dr["SectionID"]);

							CreateRootSectionInformationFromIDataReader(dr , ref section);

							//This is not the best way to do this however this will be cached for long periods
							using(SqlConnection connection2 = new SqlConnection( ConnectionString ))
							{
								SqlCommand myCommand2 = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", connection2);
								myCommand2.CommandType = CommandType.StoredProcedure;
							
								// Add Parameters to SPROC
								myCommand2.Parameters.Add(this.SettingsIDParameter());
								myCommand2.Parameters.Add("@SectionID", SqlDbType.Int).Value = section.SectionID;
								myCommand2.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = (short)ExApplicationType.ResLink;

								// Execute the command
								connection2.Open();
	
								using (SqlDataReader dr2 = myCommand2.ExecuteReader(CommandBehavior.CloseConnection))
								{
									while (dr2.Read()) 
									{
										AddSectionPermissions(dr2 , ref section);
									}
								}
							}
							// add all sections into the Hashtable
							sections.Add(section);
						}
					}
				}
				finally
				{
					myConnection.Close();
				}
			}
			return sections;	
		}

		public override PostSet SearchReindexPosts (int setsize, int settingsID) 
		{
			PostSet postSet = new PostSet();

			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_ResLink_Search_PostReindex", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@RowCount", SqlDbType.Int).Value = setsize;
				myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
                
				myConnection.Open();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult)) 
				{
					while(dr.Read())
					{
						ResLinkLink link = CreateLinksInformationFromIDataReader(dr);

						postSet.Posts.Add(link);
					}
					dr.Close();
				}

				// Close the connection
				myConnection.Close();
				return postSet;
			}		
		}

		public override SearchResultSet GetSearchResults(SearchQuery query, SearchTerms terms) 
		{
			/*
			string searchSQL = SqlGenerator.SearchText(query,terms,GetSettingsID(),(ApplicationType)ExApplicationType.ResLink);//,out searchSQL,out recordCount);

			SearchResultSet result = new SearchResultSet();
			DateTime searchDuration;
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand command = new SqlCommand("cs_ResLink_Search", connection);
				SqlDataReader reader;

				// Mark as stored procedure
				command.CommandType = CommandType.StoredProcedure;

				// Add parameters
				command.Parameters.Add("@SearchSQL", SqlDbType.NText).Value = searchSQL;
				command.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
				command.Parameters.Add("@PageSize", SqlDbType.Int).Value = query.PageSize;
				command.Parameters.Add(this.SettingsIDParameter());

				connection.Open();
				using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
				{

					// Process first record set 
					while (reader.Read()) 
					{
						ResLinkLink p = CreateLinksInformationFromIDataReader(reader);

						result.Posts.Add(p);
					}

					// Move to the next result
					if (reader.NextResult()) 
					{
						reader.Read();
						result.TotalRecords = Convert.ToInt32(reader[0]);
					}

					// Get the duration of the search?
					if (reader.NextResult()) 
					{
						reader.Read();

						searchDuration = (DateTime) reader["Duration"];

						// Calculate the number of seconds it took the search to execute
						//
						int ms = Convert.ToInt32(searchDuration.ToString("ff"));
						result.SearchDuration = (double) ms / 1000;
					}
					reader.Close();
				}

				connection.Close();

				return result;
			}
			*/
			throw new Exception("searching not implimened");
		}

		#endregion

		#region Helpers
		protected SqlConnection GetSqlConnection () 
		{

			try 
			{
				return new SqlConnection(ConnectionString);
			} 
			catch 
			{
				throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
			}

		}

		public string ConnectionString 
		{
			get 
			{
				return connectionString;
			}
			set 
			{
				connectionString = value;
			}
		}
		#endregion
	}
}
